package com.epam.edu.dao;

import com.epam.edu.constant.Language;
import com.epam.edu.entity.*;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DAOAdmission {
    public static int add(Connection connection, Admission admission) {
        int id = -1;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            int specialtyId = admission.getSpecialty().getId();
            Language language = admission.getLanguage();
            int teachingFormId = admission.getTeachingForm().getId();
            int teachingPlanId = admission.getTeachingPlan().getId();
            int fact = DAOOrder.getOrdersCountByAdmissions(connection, specialtyId, admission.getGroup(),
                    language, teachingFormId, teachingPlanId);
            admission.setFactCount(fact);
            String command = String.format("INSERT INTO " +
                    "Admissions(specId,groupName,eduLanguage,tfId,tpId,planCount,factCount) " +
                    "VALUES(?,?,?,?,?,?,?)",
                    specialtyId, teachingFormId, teachingPlanId, admission.getPlanCount(), admission.getFactCount());
            statement = connection.prepareStatement(command, Statement.RETURN_GENERATED_KEYS);
            statement.setInt(1, specialtyId);
            statement.setString(2, admission.getGroup());
            statement.setString(3, language.toString());
            statement.setInt(4, teachingFormId);
            statement.setInt(5, teachingPlanId);
            statement.setInt(6, admission.getPlanCount());
            statement.setInt(7, admission.getFactCount());
            statement.executeUpdate();
            resultSet = statement.getGeneratedKeys();
            resultSet.next();
            id = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return id;
    }

    public static void update(Connection connection, Admission admission) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            int specialtyId = admission.getSpecialty().getId();
            String group = admission.getGroup();
            Language language = admission.getLanguage();
            int teachingFormId = admission.getTeachingForm().getId();
            int teachingPlanId = admission.getTeachingPlan().getId();
            int fact = DAOOrder.getOrdersCountByAdmissions(connection, specialtyId, group,
                    language, teachingFormId, teachingPlanId);
            admission.setFactCount(fact);
            String command = String.format("UPDATE Admissions SET " +
                    "specId=?,groupName=?,eduLanguage=?, tfId=?,tpId=?,planCount=?,factCount=? " +
                    "WHERE id=?");
            statement = connection.prepareStatement(command);
            statement.setInt(1, specialtyId);
            statement.setString(2, group);
            statement.setString(3, language.toString());
            statement.setInt(4, teachingFormId);
            statement.setInt(5, teachingPlanId);
            statement.setInt(6, admission.getPlanCount());
            statement.setInt(7, admission.getFactCount());
            statement.setInt(8, admission.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void delete(Connection connection, Admission admission) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("UPDATE Admissions SET isDelete='true' WHERE id=%s", admission.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void remove(Connection connection, Admission admission) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("DELETE Admissions WHERE id=%s", admission.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static Admission selectById(Connection connection, int id) {
        Admission admission = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,specId,groupName,eduLanguage,tfId,tpId,planCount,factCount " +
                    " FROM Admissions WHERE id=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection, resultSet.getInt(2));
                Language language = Language.valueOf(resultSet.getString(4));
                TeachingForm teachingForm = DAOTeachingForm.selectById(connection, resultSet.getInt(5));
                TeachingPlan teachingPlan = DAOTeachingPlan.selectById(connection, resultSet.getInt(6));
                int fact = DAOOrder.getOrdersCountByAdmissions(connection, resultSet.getInt(2), resultSet.getString(3),
                        language, resultSet.getInt(5), resultSet.getInt(6));
                admission = new Admission
                        (resultSet.getInt(1), specialty, resultSet.getString(3), language, teachingForm,
                                teachingPlan, resultSet.getInt(7), fact, fact - resultSet.getInt(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return admission;
    }

    public static List<Admission> selectAll(Connection connection) {
        List<Admission> admissions = new LinkedList<Admission>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,specId,groupName,eduLanguage,tfId,tpId,planCount,factCount " +
                    "FROM Admissions WHERE isDelete='false'";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection, resultSet.getInt(2));
                Language language = Language.valueOf(resultSet.getString(4));
                TeachingForm teachingForm = DAOTeachingForm.selectById(connection, resultSet.getInt(5));
                TeachingPlan teachingPlan = DAOTeachingPlan.selectById(connection, resultSet.getInt(6));
                int fact = DAOOrder.getOrdersCountByAdmissions(connection, resultSet.getInt(2), resultSet.getString(3),
                        language, resultSet.getInt(5), resultSet.getInt(5));
                Admission admission = new Admission
                        (resultSet.getInt(1), specialty, resultSet.getString(3), language, teachingForm,
                                teachingPlan, resultSet.getInt(7), fact, fact - resultSet.getInt(7));
                admissions.add(admission);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return admissions;
    }

    public static String getGroup(Connection connection, int specialtyId, Language language, int tfId, int tpId) {
        String group = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT groupName " +
                    "FROM Admissions " +
                    "WHERE specId=? AND eduLanguage=? AND " +
                    "tfId=? AND tpId=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, specialtyId);
            statement.setString(2, language.toString());
            statement.setInt(3, tfId);
            statement.setInt(4, tpId);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                group = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return group;
    }

    public static List<Admission> selectByMarks(Connection connection, InputMark inputMark,
                                                TeachingPlan teachingPlan, TeachingForm teachingForm, Language language) {
        List<Admission> admissions = new LinkedList<Admission>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT adm.id,adm.specId,adm.groupName,adm.eduLanguage, " +
                    "adm.tfId,adm.tpId,adm.planCount,adm.factCount FROM Admissions adm " +
                    "INNER JOIN InputMarks im ON adm.specID=im.specId " +
                    "WHERE im.historyKZ<=? " +
                    "      AND im.language<=?" +
                    "      AND im.mathematics<=?" +
                    "      AND im.subjId=?" +
                    "      AND im.mark<=?" +
                    "      AND adm.eduLanguage=?" +
                    "      AND adm.tfId=?" +
                    "      AND adm.tpId=?" +
                    "      AND adm.planCount>adm.factCount " +
                    "      AND adm.isDelete='false' AND im.isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, inputMark.getHistoryKZMark());
            statement.setInt(2, inputMark.getLanguageMark());
            statement.setInt(3, inputMark.getMathematicsMark());
            statement.setInt(4, inputMark.getSubject().getId());
            statement.setInt(5, inputMark.getMark());
            statement.setString(6, language.toString());
            statement.setInt(7, teachingForm.getId());
            statement.setInt(8, teachingPlan.getId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = DAOSpecialty.selectById(connection, resultSet.getInt(2));
                int fact = DAOOrder.getOrdersCountByAdmissions(connection, resultSet.getInt(2), resultSet.getString(3),
                        language, teachingForm.getId(), teachingPlan.getId());
                Admission admission = new Admission
                        (resultSet.getInt(1), specialty, resultSet.getString(3), language, teachingForm,
                                teachingPlan, resultSet.getInt(7), fact, fact - resultSet.getInt(7));
                admissions.add(admission);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return admissions;
    }
}
